﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace GuiAndDb
{
    class ProductDB
    {
        static string connectionString = "data source=.;Initial Catalog=ShopDB;Integrated Security=True";

        public void Add(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string quesry = string.Format("Insert into MyTable1 (barCode, name, price, quantity, expirationDate, unitOfMeasure,department) VALUES('{0}', '{1}', '{2}', '{3}', '{4}','{5}','{6}')", p.barCode, p.name, p.price, p.quantity, p.expirationDate, p.unitOfMeasure, p.department);
                SqlCommand command = new SqlCommand(quesry, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public List<Product> GetPatients()
        {
            List<Product> result = new List<Product>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = "select * from MyTable1";
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Product p = new Product();
                    p.barCode = (string)reader["barCode"];
                    p.name = (string)reader["name"];
                    p.price = (int)reader["price"];
                    p.quantity = (int)reader["quantity"];
                    p.expirationDate = (DateTime)reader["expirationDate"];
                    p.unitOfMeasure = (string)reader["unitOfMeasure"];
                    p.department = (int)reader["department"];
                    result.Add(p);
                }
                reader.Close();
            }

            return result;
        }

        public void Update(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("Update MyTable1 set name = '{1}', price = '{2}', quantity = '{3}', expirationDate = '{4}', unitOfMeasure = '{5}', department = '{6}'  WHERE barCode = '{0}'", p.barCode, p.name, p.price, p.quantity, p.expirationDate, p.unitOfMeasure, p.department);
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void Delete(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("Delete from MyTable1 WHERE barCode = {0}", p.barCode);
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public string GenerateBarCode(int department)
        {
            string barCode = department.ToString();

            Random random = new Random();
            for (int i = 0; i < 7; i++)
            {
                char ch = (char)random.Next('0', '9' + 1);
                string ss = ch.ToString();
                barCode = barCode + ss;
            }
            if (!CheckBarCode(barCode))
            {
                barCode = GenerateBarCode(department);
            }
            return barCode;
        }

        public bool CheckBarCode(string newBarCode)
        {
            bool isAvailable = true;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("select * from MyTable1 where barCode = '{0}'", newBarCode);
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.Read())
                {
                    isAvailable = false;
                }
                reader.Close();
            }
            return isAvailable;
        }

        public int GetProductPrice(string barCode)
        {
            int price = 0;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("select * from MyTable1 where barCode = '{0}'", barCode);
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    price = (int)reader["price"];
                }
                reader.Close();
            }
            return price;
        }


        public Product GetProductInfo(string barCode)
        {
            Product p = new Product();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("select * from MyTable1 where barCode = '{0}'", barCode);
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    p.barCode = (string)reader["barCode"];
                    p.name = (string)reader["name"];
                    p.price = (int)reader["price"];
                    p.quantity = (int)reader["quantity"];
                    p.expirationDate = (DateTime)reader["expirationDate"];
                    p.unitOfMeasure = (string)reader["unitOfMeasure"];
                    p.department = (int)reader["department"];
                }
                reader.Close();
            }
            return p;
        }
    }
}


